#include "database.h"

// SQLite 函数声明
extern "C" {
    const char* sqlite3_libversion(void);
}

// Database operations class implementation
Database::Database() {
    db = nullptr;
}

Database::~Database() {
    // Close database connection
    if (db) {
        sqlite3_close(db);
    }
}

// Constructor with path parameter
Database::Database(const std::string& dbPath) : db(nullptr) {
    if (!open(dbPath)) {
        std::cerr << "Failed to open database: " << dbPath << std::endl;
    } else {
        std::cout << "Successfully opened database: " << dbPath << std::endl;
        initGeographicTables();
        initializeDatabase(); // 初始化diary相关表
    }
}

// Open database connection
bool Database::open(const std::string& dbPath) {
    // Close previous connection (if exists)
    if (db) {
        sqlite3_close(db);
    }
    
    // Open database connection
    int rc = sqlite3_open(dbPath.c_str(), &db);
    if (rc) {
        std::cerr << "Cannot open database: " << sqlite3_errmsg(db) << std::endl;
        return false;
    }

    // Create users table
    const char* createUserTableSQL = 
        "CREATE TABLE IF NOT EXISTS users ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "username TEXT NOT NULL UNIQUE,"
        "password TEXT NOT NULL);";

    // Execute SQL statement to create users table
    char* errMsg = nullptr;
    rc = sqlite3_exec(db, createUserTableSQL, nullptr, nullptr, &errMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << errMsg << std::endl;
        sqlite3_free(errMsg);
        return false;
    }
    
    // Initialize geographic data tables
    bool result = initGeographicTables();
    
    // Initialize diary tables
    if (result) {
        try {
            initializeDatabase();
        } catch (const std::exception& e) {
            std::cerr << "Failed to initialize diary tables: " << e.what() << std::endl;
            return false;
        }
    }
    
    // 检查SQLite版本和FTS5支持
    std::cout << "SQLite版本: " << sqlite3_libversion() << std::endl;
    
    // 测试FTS5是否真正可用
    const char* test_fts_sql = "CREATE VIRTUAL TABLE IF NOT EXISTS test_fts_check USING fts5(test_content);";
    char* test_errMsg = nullptr;
    bool fts5_available = (sqlite3_exec(db, test_fts_sql, nullptr, nullptr, &test_errMsg) == SQLITE_OK);
    
    if (fts5_available) {
        std::cout << "FTS5功能测试通过" << std::endl;
        // 清理测试表
        sqlite3_exec(db, "DROP TABLE IF EXISTS test_fts_check;", nullptr, nullptr, nullptr);
    } else {
        std::cout << "FTS5功能测试失败: " << (test_errMsg ? test_errMsg : "未知错误") << std::endl;
        if (test_errMsg) sqlite3_free(test_errMsg);
    }
    
    return result;
}

// Register new user
bool Database::registerUser(const std::string& username, const std::string& password) {
    // Check if user already exists
    if (userExists(username)) {
        return false;
    }

    // Insert new user
    std::string sql = "INSERT INTO users (username, password) VALUES (?, ?);";
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr);

    if (rc != SQLITE_OK) {
        std::cerr << "Failed to prepare SQL statement: " << sqlite3_errmsg(db) << std::endl;
        return false;
    }

    // Bind parameters
    rc = sqlite3_bind_text(stmt, 1, username.c_str(), -1, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK) {
        std::cerr << "Failed to bind username parameter: " << sqlite3_errmsg(db) << std::endl;
        sqlite3_finalize(stmt);
        return false;
    }

    rc = sqlite3_bind_text(stmt, 2, password.c_str(), -1, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK) {
        std::cerr << "Failed to bind password parameter: " << sqlite3_errmsg(db) << std::endl;
        sqlite3_finalize(stmt);
        return false;
    }

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    return rc == SQLITE_DONE;
}

// Verify user login
int Database::loginUser(const std::string& username, const std::string& password) {
    sqlite3_stmt* stmt;
    const char* sql = "SELECT id FROM users WHERE username = ? AND password = ?";
    
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
        return -1;
    }
    
    sqlite3_bind_text(stmt, 1, username.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, password.c_str(), -1, SQLITE_STATIC);
    
    int result = -1;
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        result = sqlite3_column_int(stmt, 0);
    }
    
    sqlite3_finalize(stmt);
    return result;
}

// Check if user exists
bool Database::userExists(const std::string& username) {
    std::string sql = "SELECT 1 FROM users WHERE username = ?;";
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr);

    if (rc != SQLITE_OK) {
        std::cerr << "Failed to prepare SQL statement: " << sqlite3_errmsg(db) << std::endl;
        return false;
    }

    rc = sqlite3_bind_text(stmt, 1, username.c_str(), -1, SQLITE_TRANSIENT);
    if (rc != SQLITE_OK) {
        std::cerr << "Failed to bind username parameter: " << sqlite3_errmsg(db) << std::endl;
        sqlite3_finalize(stmt);
        return false;
    }

    bool exists = (sqlite3_step(stmt) == SQLITE_ROW);
    sqlite3_finalize(stmt);

    return exists;
}

// Execute generic SQL statement
bool Database::executeSQL(const std::string& sql) {
    char* errMsg = nullptr;
    int rc = sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &errMsg);
    
    if (rc != SQLITE_OK) {
        std::cerr << "Failed to execute SQL: " << errMsg << std::endl;
        sqlite3_free(errMsg);
        return false;
    }
    
    return true;
}

// Initialize geographic data tables
bool Database::initGeographicTables() {
    bool success = true;
    
    // Create places table
    const char* createPlacesTableSQL = 
        "CREATE TABLE IF NOT EXISTS places ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "name TEXT NOT NULL,"
        "type TEXT NOT NULL,"
        "latitude REAL NOT NULL,"
        "longitude REAL NOT NULL,"
        "outline TEXT,"             // GeoJSON format outline data
        "description TEXT,"
        "address TEXT);";
        
    // Create internal places table
    const char* createInternalPlacesTableSQL =
        "CREATE TABLE IF NOT EXISTS internal_places ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "parent_id INTEGER NOT NULL,"
        "name TEXT NOT NULL,"
        "type TEXT NOT NULL,"
        "latitude REAL NOT NULL,"
        "longitude REAL NOT NULL,"
        "description TEXT,"
        "FOREIGN KEY (parent_id) REFERENCES places(id));";
        
    // Create roads table
    const char* createRoadsTableSQL =
        "CREATE TABLE IF NOT EXISTS roads ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "start_lat REAL NOT NULL,"
        "start_lng REAL NOT NULL,"
        "end_lat REAL NOT NULL,"
        "end_lng REAL NOT NULL,"
        "road_type TEXT);";
    
    // Create routes table
    const char* createRoutesTableSQL =
        "CREATE TABLE IF NOT EXISTS routes ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "name TEXT NOT NULL,"
        "description TEXT,"
        "duration INTEGER,"         // Estimated time (minutes)
        "distance REAL);";          // Total distance (kilometers)
        
    // Create route-places association table
    const char* createRoutePlacesTableSQL =
        "CREATE TABLE IF NOT EXISTS route_places ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "route_id INTEGER NOT NULL,"
        "place_id INTEGER NOT NULL,"
        "visit_order INTEGER NOT NULL,"
        "stay_time INTEGER,"        // Stay time (minutes)
        "FOREIGN KEY (route_id) REFERENCES routes(id),"
        "FOREIGN KEY (place_id) REFERENCES places(id));";
    
    // Execute SQL statements to create tables
    success = success && executeSQL(createPlacesTableSQL);
    success = success && executeSQL(createInternalPlacesTableSQL);
    success = success && executeSQL(createRoadsTableSQL);
    success = success && executeSQL(createRoutesTableSQL);
    success = success && executeSQL(createRoutePlacesTableSQL);
    
    return success;
}

// Initialize diary tables
void Database::initializeDatabase() {
    const char* sql = R"(
        CREATE TABLE IF NOT EXISTS travel_diary (
            diary_id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            compressed_content BLOB NOT NULL,
            destination TEXT,
            views INTEGER DEFAULT 0,
            average_rating REAL DEFAULT 3.0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );
        
        CREATE TABLE IF NOT EXISTS diary_rating (
            rating_id INTEGER PRIMARY KEY AUTOINCREMENT,
            diary_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            rating REAL CHECK (rating BETWEEN 1 AND 5),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (diary_id) REFERENCES travel_diary(diary_id),
            UNIQUE (diary_id, user_id)
        );
        
        CREATE INDEX IF NOT EXISTS idx_diary_destination ON travel_diary(destination);
        CREATE INDEX IF NOT EXISTS idx_diary_heat ON travel_diary(views DESC);
        CREATE INDEX IF NOT EXISTS idx_diary_rating ON travel_diary(average_rating DESC);
        CREATE INDEX IF NOT EXISTS idx_rating_diary ON diary_rating(diary_id);
        
        CREATE TRIGGER IF NOT EXISTS update_avg_rating AFTER INSERT ON diary_rating
        BEGIN
            UPDATE travel_diary
            SET average_rating = (
                SELECT AVG(rating)
                FROM diary_rating
                WHERE diary_id = NEW.diary_id
            )
            WHERE diary_id = NEW.diary_id;
        END;
    )";
    
    char* errMsg = nullptr;
    if (sqlite3_exec(db, sql, nullptr, nullptr, &errMsg) != SQLITE_OK) {
        std::string error = "数据库初始化失败: " + std::string(errMsg);
        sqlite3_free(errMsg);
        throw std::runtime_error(error);
    }
    
    // 尝试创建FTS表（如果SQLite支持FTS5）
    const char* fts_sql = R"(
        CREATE VIRTUAL TABLE IF NOT EXISTS diary_fts USING fts5(
            diary_id UNINDEXED,
            title,
            content
        );
    )";
    
    if (sqlite3_exec(db, fts_sql, nullptr, nullptr, &errMsg) != SQLITE_OK) {
        std::cerr << "警告: 无法创建FTS表，全文搜索功能将不可用: " << errMsg << std::endl;
        sqlite3_free(errMsg);
        // 不抛出异常，因为FTS是可选功能
    }
} 